package br.edu.univas.si.es.correios.test.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import br.edu.univas.si.es.correios.model.ConectDB;
import br.edu.univas.si.es.correios.model.OrderTO;


public class CorreiosMockDAO extends ConectDB{
	
	public void create(OrderTO order){
		String sqlCreatPedidos = "insert into pedidos(num_rastreamento, modo_envio, cep_emit, cep_dest, estado_emit, estado_dest, cid_emit, cid_dest," +
								 "bairro_emit, bairro_dest, log_emit, log_dest, num_emit, num_dest, preco, prazo) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		
		try{
			PreparedStatement pedidos = getConnection().prepareStatement(sqlCreatPedidos);
			
			pedidos.setInt(1, order.getOrderNumber());
			pedidos.setString(2, order.getSendMode());
			pedidos.setInt(3, order.getSenderCep());
			pedidos.setInt(4, order.getReciverCep());
			pedidos.setString(5, order.getSenderState());
			pedidos.setString(6, order.getDestinataryState());
			pedidos.setString(7, order.getSenderCity());
			pedidos.setString(8, order.getDestinataryCity());
			pedidos.setString(9, order.getSenderNeighbor());
			pedidos.setString(10, order.getDestinataryNeighbor());
			pedidos.setString(11, order.getSenderStreet());
			pedidos.setString(12, order.getDestinataryStreet());
			pedidos.setInt(13, order.getSenderStreetNumber());
			pedidos.setInt(14, order.getDestinataryStreetNumber());
			pedidos.setFloat(15, order.getTotalCosts());
			pedidos.setInt(16, order.getDeliveryDays());
			pedidos.executeUpdate();
			pedidos.close();
			
		}catch(Exception e){
			System.out.println("Erro: " + e.getMessage());
			e.printStackTrace();
		}
	}
	
	public void retrieveOne(int orderNumber, OrderTO order){
	
//				order.setOrderNumber();
//				order.setSendMode(result.getString("modo_envio"));
//				order.setSenderCep(result.getInt("cep_emit"));
//				order.setDestinataryCep(result.getInt("cep_dest"));
//				order.setSenderState(result.getString("estado_emit"));
//				order.setDestinataryState(result.getString("estado_dest"));
//				order.setSenderCity(result.getString("cid_emit"));
//				order.setDestinataryCity(result.getString("cid_dest"));
//				order.setSenderNeighbor(result.getString("bairro_emit"));
//				order.setDestinataryNeighbor(result.getString("bairro_dest"));
//				order.setSenderStreet(result.getString("log_emit"));
//				order.setDestinataryStreet(result.getString("log_dest"));
//				order.setSenderStreetNumber(result.getInt("num_emit"));
//				order.setDestinataryStreetNumber(result.getInt("num_dest"));
//				order.setTotalCosts(result.getInt("preco"));
//				order.setDeliveryDays(result.getInt("prazo"));
	
	}
	
	public boolean returnSenderCityState(OrderTO order){

		String sqlRetrieveCity = "select cidade from cidades where cidades.cep = '" + order.getSenderCep() + "'";
		String sqlRetrieveState = "select estado from cidades where cidades.cep = '" + order.getSenderCep() + "'";
		
		try {			
			Statement statement = getStatement();
			
			ResultSet resultCity = statement.executeQuery(sqlRetrieveCity);
			resultCity.next();
			order.setSenderCity(resultCity.getString(1));
		
			
			ResultSet resultState = statement.executeQuery(sqlRetrieveState);	
			resultState.next();
			order.setSenderState(resultState.getString(1));				

			
		} catch (SQLException e) {
			System.out.println("CEP INEXISTENTE!!");
			//System.out.println("Erro: " + e.getMessage());
			//e.printStackTrace();
			return false;
		}
		return true;
}
	
	public boolean returnDestinataryCityState(OrderTO order){

		String sqlRetrieveCity = "select cidade from cidades where cidades.cep = '" + order.getReciverCep() + "'";
		String sqlRetrieveState = "select estado from cidades where cidades.cep = '" + order.getReciverCep() + "'";
		
		try {			
			Statement statement = getStatement();
			
			ResultSet resultCity = statement.executeQuery(sqlRetrieveCity);
			resultCity.next();
			order.setDestinataryCity(resultCity.getString(1));
			
			ResultSet resultState = statement.executeQuery(sqlRetrieveState);	
			resultState.next();
			order.setDestinataryState(resultState.getString(1));				

		} catch (SQLException e) {
			//System.out.println("Erro: " + e.getMessage());
			System.out.println("CEP INEXISTENTE!!");
			//e.printStackTrace();
			return false;
		}
		return true;
}
	
	public void update(OrderTO order, String field, String newData){
		String sql = "update pedidos set " + field  + " = ? where pedidos.num_rastreamento = " + order.getOrderNumber(); 
						
		try {
			PreparedStatement statement = getConnection().prepareStatement(sql);
			statement.setString(1,newData);
			
		} catch (SQLException e) {
			System.out.println("ERRO AO TENTAR MODIFICAR O PEDIDO!!");
			e.printStackTrace();
		}
		
	}
	
	public void delete(OrderTO order){	
		String sql = "delete from pedidos where pedidos.num_rastreamento = '" + order.getOrderNumber() + "'";
		
		try {
			PreparedStatement statement = getConnection().prepareStatement(sql);
			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public boolean checkOrderNumber(int orderNumber) {
		String sql = "select num_rastreamento from pedidos where pedidos.num_rastreamento = '" + orderNumber + "'";
		
		try {			
			Statement statement = getStatement();
			
			ResultSet resultCity = statement.executeQuery(sql);
			resultCity.next();
			orderNumber = (resultCity.getInt("num_rastreamento"));
							

		} catch (SQLException e) {
			//System.out.println("Erro: " + e.getMessage());
			System.out.println("ENVIO INEXISTENTE!!");
			//e.printStackTrace();
			return false;
		}
		return true;
	}

	public void getDeliveryDays(OrderTO order) {
		String sql = " select " + order.getSendMode() + " from prazos where prazos.estado = '" + order.getDestinataryState() + "'";
		
		try {			
			Statement statement = getStatement();
			
			ResultSet resultSet = statement.executeQuery(sql);
			resultSet.next();
			order.setDeliveryDays(resultSet.getInt(1));				

		} catch (SQLException e) {
			//System.out.println("Erro: " + e.getMessage());
			System.out.println("ESTADO INEXISTENTE!!");
			//e.printStackTrace();

		}
	}
}
